package connectivity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Category;
import model.Product;
import model.User;

public class QueryManager {

	private final Dbmanager dbmanager;

	public QueryManager(Dbmanager dbmanager) {
		this.dbmanager = dbmanager;
	}

	public String getCategoryName(int categoryId) {
		String categoryName = "";
		try {
			String sql = "SELECT naam FROM categorie WHERE categorie_id='" + categoryId + "'";
			ResultSet result = dbmanager.doQuery(sql);
			while (result.next()) {
				categoryName = result.getString("naam");
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return categoryName;
	}

	public List<Category> getCategories() {
		List<Category> categories = new ArrayList<Category>();
		try {
			String sql = "SELECT * FROM categorie ORDER BY naam ASC";
			ResultSet result = dbmanager.doQuery(sql);
			while (result.next()) {
				Category category = new Category(result.getInt("categorie_id"),
						result.getString("naam"),
						result.getString("omschrijving"));
				categories.add(category);
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return categories;
	}

	public Product getProduct(int productId) {
		Product product = new Product();
		try {
			String sql = "SELECT * FROM product WHERE product_id='" + productId + "'";
			ResultSet result = dbmanager.doQuery(sql);
			if (result.next()) {
				product = new Product(result.getInt("product_id"),
						result.getInt("categorie_id"),
						result.getString("naam"),
						result.getString("omschrijving"),
						result.getDouble("prijs"));
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return product;
	}

	public List<Product> getProducts(int categoryId) {
		List<Product> products = new ArrayList<Product>();
		try {
			String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "' ORDER BY naam ASC";
			ResultSet result = dbmanager.doQuery(sql);
			while (result.next()) {
				products.add(new Product(result.getInt("product_id"),
						result.getInt("categorie_id"),
						result.getString("naam"),
						result.getString("omschrijving"),
						result.getDouble("prijs")));
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return products;
	}

	public void setOrder(model.Basket basket, String naam, String adres,
			String postcode, String woonplaats, String opmerking, String betaalmethode) {
		String SQL_order = "INSERT INTO `order` (naam, adres, postcode, woonplaats, notes, betaalmethode, datum)"
				+ " VALUES('" + naam + "', '" + adres + "', '" + postcode + "', '"
				+ woonplaats + "', '" + opmerking + "', '" + betaalmethode + "', CURDATE() )";
		int order_id = 0;
		try {
			ResultSet result = dbmanager.insertQuery(SQL_order);
			result.next();
			order_id = result.getInt(1);
		} catch (SQLException e) {
			System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
		}
		List<Product> products = basket.getProducts();
		for (Product product : products) {
			int product_id = product.getProductId();
			int aantal = basket.getProductAmount(product);
			String SQL_orderProduct = "INSERT INTO orderregel (product_id,order_id,aantal) VALUES (" + product_id + "," + order_id + "," + aantal + ")";
			dbmanager.insertQuery(SQL_orderProduct);
		}
	}
	
	/* User Queries */
	
	/**
	 * getUsers
	 * @return list of Users (each user with his own User model)
	 */
	public List getUsers() {
		ArrayList Users = new ArrayList();
		try {
			String sql = "SELECT * FROM gebruikers ORDER BY gebruikersnaam ASC";
			ResultSet result = dbmanager.doQuery(sql);
			while (result.next())
			{
				
				ArrayList user = new ArrayList();
				user.add(result.getInt("id"));
				user.add(result.getString("gebruikersnaam"));
				user.add(result.getString("voornaam"));
				user.add(result.getString("achternaam"));
				user.add(result.getString("rank"));
				
				Users.add(user);
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return Users;
	}
	
	/**
	 * checkUserLogin - checks if a user can be logged in
	 * @param username String
	 * @param password String
	 * @return User object (returns null instead when user cannot be found)
	 */
	public User checkUserLogin(String username, String password)
	{
		User user = null;
		try
		{
			String sql = "SELECT * FROM gebruikers WHERE `gebruikersnaam`='" + username + "' AND `wachtwoord`='" + password + "'";
			ResultSet result = dbmanager.doQuery(sql);
			if (result.next())
			{
				user = new User(
				result.getInt("id"),
				result.getString("gebruikersnaam"),
				result.getString("voornaam"),
				result.getString("achternaam"),
				result.getInt("rank"));
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return user;
	}
	
	/**
	 * getUser (type + string value)
	 * @param type String
	 * @param value String
	 * @return User object
	 */
	public User getUser(String type, String value)
	{
		User user = null;
		
		try
		{
			String sql = "SELECT * FROM gebruikers WHERE `" + type + "`='" + value + "'";
			ResultSet result = dbmanager.doQuery(sql);
			if (result.next())
			{
				user = new User(
				result.getInt("id"),
				result.getString("gebruikersnaam"),
				result.getString("voornaam"),
				result.getString("achternaam"),
				result.getInt("rank"));
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		return user;
	}
	
	/**
	 * getUser (type + integer value)
	 * @param type String
	 * @param value Integer
	 * @return User object
	 */
	public User getUser(String type, int value)
	{
		User user = null;
		
		try
		{
			String sql = "SELECT * FROM gebruikers WHERE `" + type + "`='" + value + "'";
			ResultSet result = dbmanager.doQuery(sql);
			if (result.next())
			{
				user = new User(
				result.getInt("id"),
				result.getString("gebruikersnaam"),
				result.getString("voornaam"),
				result.getString("achternaam"),
				result.getInt("rank"));
			}
		} catch (SQLException e) {
			System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
		}
		
		return user;
	}
	
	/**
	 * addUser - adds a user
	 * @param firstname String
	 * @param lastname String
	 * @param username String
	 * @param password String
	 * @return int ID of new user
	 */
	public int addUser(String firstname, String lastname, String username, String password)
	{
		String SQL_order = "INSERT INTO `gebruikers` (gebruikersnaam, voornaam, achternaam, wachtwoord) VALUES ('" + username + "', '" + firstname + "', '" + lastname + "', '" + password + "')";
		int user_id = 0;
		try {
			ResultSet result = dbmanager.insertQuery(SQL_order);
			result.next();
			user_id = result.getInt(1);
		} catch (SQLException e) {
			System.out.println("connectivity.QueryManager.addUser() Exception:" + e.getMessage());
		}
		
		return user_id;
	}
	
	/**
	 * UNTESTED FUNCTION - deleteUser Removes User with the given ID
	 * @param id
	 * @return boolean whether successful or not.
	 */
	public boolean deleteUser(int id)
	{
		try
		{
			String SQL_order = "DELETE * FROM `gebruikers` WHERE `id`='" + id + "')";
			ResultSet result = dbmanager.doQuery(SQL_order);
			result.next();
			
			return true;
		}
		catch (SQLException e)
		{
			System.out.println("connectivity.QueryManager.addUser() Exception:" + e.getMessage());
		}
		
		return false;
	}
	
	/**
	 * updateUser - updates user
	 * @param id ID of the user that has to be updated
	 * @param changes List of changes row=>value
	 */
	public void updateUser(int id, ArrayList changes)
	{
		// @todo update user function
	}
}
